package com.ruoyi.hd.mapper;

import java.util.List;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.hd.domain.HdCommodityTime;
import org.apache.ibatis.annotations.Select;

/**
 * 商品记录管理Mapper接口
 * 
 * @author ruoyi
 * @date 2022-07-19
 */
public interface HdCommodityTimeMapper{

    /**
     * 查询商品记录管理
     * 
     * @param id 商品记录管理主键
     * @return 商品记录管理
     */
    public HdCommodityTime selectHdCommodityTimeById(Long id);

    /**
     * 查询商品记录管理列表
     * 
     * @param hdCommodityTime 商品记录管理
     * @return 商品记录管理集合
     */
    public List<HdCommodityTime> selectHdCommodityTimeList(HdCommodityTime hdCommodityTime);

    /**
     * 新增商品记录管理
     * 
     * @param hdCommodityTime 商品记录管理
     * @return 结果
     */
    public int insertHdCommodityTime(HdCommodityTime hdCommodityTime);

    /**
     * 修改商品记录管理
     * 
     * @param hdCommodityTime 商品记录管理
     * @return 结果
     */
    public int updateHdCommodityTime(HdCommodityTime hdCommodityTime);

    /**
     * 删除商品记录管理
     * 
     * @param id 商品记录管理主键
     * @return 结果
     */
    public int deleteHdCommodityTimeById(Long id);

    /**
     * 批量删除商品记录管理
     * 
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteHdCommodityTimeByIds(Long[] ids);

    public List<HdCommodityTime> selectHdCommodityTimeByIds(Long [] ids);
    /**
     * 昨天
     * @return
     */
    @Select({"    SELECT  DATE_FORMAT(c.create_time, '%H') as dateTime,c.price  FROM hd_commodity_time c WHERE c.commodity_id = #{commodityId} AND DATEDIFF(CURDATE(),DATE(c.create_time)) = 1 ORDER BY dateTime"})
    public List<HdCommodityTime> selectYesterdayy(HdCommodityTime hdCommodityTime);

    /**
     * 当天
     * @return
     */
    @Select({"SELECT DATE_FORMAT(c.create_time, '%h') as dateTime,c.price FROM hd_commodity_time c WHERE c.commodity_id = #{commodityId} AND to_days(c.create_time) = to_days(now()) ORDER BY dateTime"})
    public List<HdCommodityTime> selectSameDay(HdCommodityTime hdCommodityTime);
    /**
     * 近7天
     * @return
     */
    @Select({"SELECT DATE_FORMAT(cc.datelist, '%m-%d') as dateTime,(SELECT AVG(c.price) FROM hd_commodity_time c WHERE c.commodity_id = #{commodityId} AND c.create_time <= cc.datelist ) as price FROM calendar cc WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(cc.datelist) AND CURDATE() >= date(cc.datelist) ORDER BY dateTime"})
    public List<HdCommodityTime> selectSevenDay(HdCommodityTime hdCommodityTime);

    /**
     * 近30天
     * @return
     */
    @Select({"SELECT DATE_FORMAT(cc.datelist, '%m-%d') as dateTime,(SELECT AVG(c.price) FROM hd_commodity_time c WHERE c.commodity_id = #{commodityId} AND c.create_time <= cc.datelist) as price FROM calendar cc WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(cc.datelist) AND CURDATE() >= date(cc.datelist) ORDER BY dateTime"})
    public List<HdCommodityTime> selectThirtyDay(HdCommodityTime hdCommodityTime);

    /**
     * 本月
     * @return
     */
    @Select({""})
    public List<HdCommodityTime> selectThisMonth(HdCommodityTime hdCommodityTime);

    /**
     * 近六个月
     * @return
     */
    @Select({"SELECT DATE_FORMAT(cc.datelist, '%m-%d')  as dateTime,(SELECT AVG(c.price) FROM hd_commodity_time c WHERE c.commodity_id = #{commodityId} AND c.create_time <= cc.datelist) as price FROM calendar cc WHERE cc.datelist between date_sub(now(),interval 6 month) and now() ORDER BY dateTime"})
    public List<HdCommodityTime> selectSixMonth(HdCommodityTime hdCommodityTime);


    /**
     * 今年
     * @return
     */
    @Select({"SELECT DATE_FORMAT(cc.datelist, '%m-%d') as dateTime,(SELECT AVG(c.price) FROM hd_commodity_time c WHERE c.commodity_id = #{commodityId} AND c.create_time <= cc.datelist AND CURDATE() >= date(cc.datelist)) as price FROM calendar cc  WHERE YEAR(cc.datelist)=YEAR(NOW()) ORDER BY dateTime"})
    public List<HdCommodityTime> selectThisYear(HdCommodityTime hdCommodityTime);
}
